
from sqlalchemy.orm import *
from sqlalchemy.types import * 
from sqlalchemy import create_engine
from typing import Optional
from sqlalchemy import select

class Base(DeclarativeBase):
    def _asdict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}

class user(Base):
    __tablename__="user"
    id : Mapped[Integer] = mapped_column( Integer, nullable=False,primary_key=True, autoincrement=True, ) 
    username : Mapped[String] = mapped_column( String,  ) 
    password : Mapped[String] = mapped_column( String,  ) 
    height : Mapped[Double] = mapped_column( Double,  ) 
    weight : Mapped[Double] = mapped_column( Double,  ) 
    age : Mapped[Integer] = mapped_column( Integer,  ) 
    gender : Mapped[String] = mapped_column( String,  ) 
    bmi : Mapped[Double] = mapped_column( Double,  ) 
    ext : Mapped[JSON] = mapped_column( JSON,  ) 
    pics : Mapped[String] = mapped_column( String,  ) 
    role: Mapped[String] = mapped_column( String,  )
    login_time : Mapped[DateTime] = mapped_column( DateTime,  ) 
    reserve_2 : Mapped[String] = mapped_column( String,  ) 





def select_all(engine):
    with Session(engine) as session :
        return [rec._asdict() for rec in session.query(user).all()]
    
def select_by_col(engine,cond:dict):
    with Session(engine) as session :
        return [rec._asdict() for rec in session.query(user).filter_by(**cond).all()]


def update_one(engine,cond:dict,item:dict):
    try:
        with Session(engine) as session:
            record = session.query(user).filter_by(**cond).first()
            print("=========>",record)
            if record:
                # Update the record with the new values
                for key, value in item.items():
                    setattr(record, key, value)
                session.commit()
                return True
            return False 
    except Exception as e:
        print(e)
        return False
    
def add_one(engine,item:dict):
    try:
        with Session(engine) as session :
            record = user(**item)
            session.add(record)
            session.commit()
            return True 
    except Exception as e:
        print(e)
        return False
    
def delete_one(engine,cond:dict):
    try:
        with Session(engine) as session :
            record = session.query(user).filter_by(id=cond['id']).first()
            session.delete(record)
            session.commit()
            return True 
    except Exception as e:
        print(e)
        return False  
def delete_batch(engine,cond:dict):
    try:
        with Session(engine) as session :
            records = session.query(user).filter_by(**cond).all()
            for rec in records:
                session.delete(rec)
            session.commit()
            return True 
    except Exception as e:
        return False  
